/*DONE* Table [dbo].[<CompanyName>$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]  2/3  Script Date: 27.7.2020 - JI ******/
/**/
/*Add prestaging table for all companies in LSInsight$Companies table*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyCounter as INT
DECLARE @i as INT

SET @i = 0
SET @CompanyCounter = (SELECT MAX([Company]) FROM LSInsight$Companies)

WHILE @i <= @CompanyCounter 
BEGIN
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @i)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d] 
	(
	[timestamp] [timestamp] NOT NULL,
	[No_] [nvarchar](20) NOT NULL,
	[Date Created] [datetime] NOT NULL,
	[Created by User] [nvarchar](50) NOT NULL,
	[Division Code] [nvarchar](10) NOT NULL,
	[Retail Product Code] [nvarchar](20) NOT NULL,
	[Last Modified by User] [nvarchar](50) NOT NULL,
	[Item Error Check Code] [nvarchar](10) NOT NULL,
	[Item Error Check Status] [int] NOT NULL,
	[Suggested Qty_ on POS] [decimal](38, 20) NOT NULL,
	[Item Capacity Value] [decimal](38, 20) NOT NULL,
	[Qty not in Decimal] [tinyint] NOT NULL,
	[Warranty Card] [tinyint] NOT NULL,
	[Def_ Ordered by] [int] NOT NULL,
	[Def_ Ordering Method] [int] NOT NULL,
	[Original Vendor No_] [nvarchar](20) NOT NULL,
	[Original Vendor Item No_] [nvarchar](20) NOT NULL,
	[BOM Method] [int] NOT NULL,
	[BOM Receipt Print] [int] NOT NULL,
	[Recipe Version Code] [nvarchar](20) NOT NULL,
	[Recipe Item Type] [int] NOT NULL,
	[BOM Cost Price Distribution] [int] NOT NULL,
	[BOM Type] [int] NOT NULL,
	[BOM Receiving Explode] [int] NOT NULL,
	[External Item No_] [nvarchar](20) NOT NULL,
	[Extern_ Size+Crust] [nvarchar](10) NOT NULL,
	[Variant Framework Code] [nvarchar](20) NOT NULL,
	[Season Code] [nvarchar](10) NOT NULL,
	[Lifecycle Length] [varchar](32) NOT NULL,
	[Lifecycle Starting Date] [datetime] NOT NULL,
	[Lifecycle Ending Date] [datetime] NOT NULL,
	[Error Check Internal Usage] [tinyint] NOT NULL,
	[Attrib 1 Code] [nvarchar](30) NOT NULL,
	[Attrib 2 Code] [nvarchar](30) NOT NULL,
	[Attrib 3 Code] [nvarchar](30) NOT NULL,
	[Attrib 4 Code] [nvarchar](30) NOT NULL,
	[Attrib 5 Code] [nvarchar](30) NOT NULL,
	[ABC Sales] [int] NOT NULL,
	[ABC Profit] [int] NOT NULL,
	[Wastage _] [decimal](38, 20) NOT NULL,
	[Excluded from Portion Weight] [tinyint] NOT NULL,
	[Unaff_ by Multipl_ Factor] [tinyint] NOT NULL,
	[Exclude from Menu Requisition] [tinyint] NOT NULL,
	[Recipe No_ of Portions] [int] NOT NULL,
	[Max_ Modifiers No Price] [int] NOT NULL,
	[Max_ Ingr_ Removed No Price] [int] NOT NULL,
	[Max_ Ingr_ + Modifiers] [int] NOT NULL,
	[Production Time (Min_)] [decimal](38, 20) NOT NULL,
	[Recipe Main Ingredient] [nvarchar](10) NOT NULL,
	[Recipe Style] [nvarchar](10) NOT NULL,
	[Recipe Category] [nvarchar](10) NOT NULL,
	[Available as Dish] [tinyint] NOT NULL,
	[UOM Pop-up on POS] [tinyint] NOT NULL,
	[Replenishment Calculation Type] [int] NOT NULL,
	[Manual Estimated Daily Sale] [decimal](38, 20) NOT NULL,
	[Store Stock Cover Reqd (Days)] [decimal](38, 20) NOT NULL,
	[Wareh Stock Cover Reqd (Days)] [decimal](38, 20) NOT NULL,
	[Replenishment Sales Profile] [nvarchar](10) NOT NULL,
	[Replenishment Grade Code] [nvarchar](10) NOT NULL,
	[Exclude from Replenishment] [tinyint] NOT NULL,
	[Transfer Multiple] [decimal](38, 20) NOT NULL,
	[Store Forward Sales Profile] [nvarchar](10) NOT NULL,
	[Wareh_ Forward Sales Profile] [nvarchar](10) NOT NULL,
	[Purch_ Order Delivery] [int] NOT NULL,
	[Replenish as Item No_] [nvarchar](20) NOT NULL,
	[Profit Goal _] [decimal](38, 20) NOT NULL,
	[Replen_ Data Profile] [nvarchar](20) NOT NULL,
	[Like for Like Replen_ Method] [int] NOT NULL,
	[Like for Like Process Method] [int] NOT NULL,
	[Replenish as Item No - Method] [int] NOT NULL,
	[Replen_ Transfer Rule Code] [nvarchar](10) NOT NULL,
	[Select Lowest Price Vendor] [tinyint] NOT NULL,
	[Effective Inv_ Sales Order] [int] NOT NULL,
	[Effective Inv_ Purchase Ord_] [int] NOT NULL,
	[Effective Inv_ Transfer Inb_] [int] NOT NULL,
	[Effective Inv_ Transfer Outb_] [int] NOT NULL,
	[LS Forecast Method] [int] NOT NULL,
	[LS Forecast Calc_ Horizon] [int] NOT NULL,
	[Fuel Item] [tinyint] NOT NULL,
	[Item Family Code] [nvarchar](20) NOT NULL,
	[Unit Price Including VAT] [decimal](38, 20) NOT NULL,
	[POS Cost Calculation] [int] NOT NULL,
		CONSTRAINT ['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
	
EXEC(@CreateTable)

Set @CreateTable = 'ALTER TABLE [dbo].['+@Company+'$Item$5ecfc871-5d82-43f1-9c54-59685e82318d]
ADD [No Stock Posting] [tinyint] NOT NULL,
	[Zero Price Valid] [tinyint] NOT NULL,
	[Qty_ Becomes Negative] [tinyint] NOT NULL,
	[No Discount Allowed] [tinyint] NOT NULL,
	[Keying in Price] [int] NOT NULL,
	[Scale Item] [tinyint] NOT NULL,
	[Keying in Quantity] [int] NOT NULL,
	[Skip Compression When Scanned] [tinyint] NOT NULL,
	[Skip Compression When Printed] [tinyint] NOT NULL,
	[Barcode Mask] [nvarchar](22) NOT NULL,
	[Use EAN Standard Barc_] [tinyint] NOT NULL,
	[Qty_ per Base Comp_ Unit] [decimal](38, 20) NOT NULL,
	[Base Comp_ Unit Code] [nvarchar](10) NOT NULL,
	[Comparison Unit Code] [nvarchar](10) NOT NULL,
	[Comp_ Price Incl_ VAT] [decimal](38, 20) NOT NULL,
	[Explode BOM in Statem_ Posting] [tinyint] NOT NULL,
	[Dispense Printer Group] [nvarchar](10) NOT NULL,
	[Print Variants Shelf Labels] [tinyint] NOT NULL,
	[Tare Weight] [decimal](38, 20) NOT NULL,
	[Lifecycle Curve Code] [nvarchar](20) NOT NULL,
	[Dimension Pattern Code] [nvarchar](20) NOT NULL,
	[Upd_ Cost and Weight w_Posting] [tinyint] NOT NULL,
	[Store Coverage Days Profile] [nvarchar](10) NOT NULL,
	[Wareh Coverage Days Profile] [nvarchar](10) NOT NULL,
	[WIC Applicable] [tinyint] NOT NULL,
	[Food Stamp Applicable] [tinyint] NOT NULL,
	[Ask for Tare] [tinyint] NOT NULL,
	[Default Tare Weight] [decimal](38, 20) NOT NULL'

EXEC(@CreateTable)

END
SET @i = @i+1
END